package com.team.sns.utils;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class MySqlUtils {

	private static Properties pop = new Properties();
	private static final String DRIVER = "driver";
	private static final String URL = "url";
	private static final String USERNAME = "username";
	private static final String PASSWORD = "pssword";

	public static Connection getConn() {
		Connection conn = null;

		try {
			pop.load(MySqlUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
			Class.forName(pop.getProperty(MySqlUtils.DRIVER));
			conn = DriverManager.getConnection(pop.getProperty(MySqlUtils.URL), pop.getProperty(MySqlUtils.USERNAME),
					pop.getProperty(MySqlUtils.PASSWORD));
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}

		return conn;
	}

	public static PreparedStatement prepare(Connection conn, String sql) {
		PreparedStatement pstmt = null;
		try {
			if (conn != null) {
				pstmt = conn.prepareStatement(sql);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		return pstmt;
	}

	public static PreparedStatement prepare(Connection conn, String sql, int autoGenereatedKeys) {
		PreparedStatement pstmt = null;

		try {
			if (conn != null) {
				pstmt = conn.prepareStatement(sql, autoGenereatedKeys);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		return pstmt;
	}

	public static Statement getStatement(Connection conn) {
		Statement stmt = null;

		try {
			if (conn != null) {
				stmt = conn.createStatement();
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		return stmt;
	}

	public static ResultSet getResultSet(Statement stmt, String sql) {
		ResultSet rs = null;

		try {
			if (stmt != null) {
				rs = stmt.executeQuery(sql);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		return rs;
	}

	public static void executeUpdate(Statement stmt, String sql) {
		try {
			if (stmt != null) {
				stmt.executeUpdate(sql);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public static void executeCall(CallableStatement cs, Object[] params) {
		try {
			MySqlUtils.setQueryParams(cs, params);
			cs.executeQuery();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public static List<Object[]> getResultList(CallableStatement cs, Object[] params) throws SQLException {
		MySqlUtils.setQueryParams(cs, params);
		List<Object[]> resultList = new ArrayList<Object[]>();
		ResultSet rs = cs.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		Object[] objs = null;

		while (rs.next()) {
			objs = new Object[columnCount];
			for (int i = 0; i < columnCount; i++) {
				objs[i] = rs.getObject(i + 1);
			}
			resultList.add(objs);
		}

		MySqlUtils.close(rs);
		return resultList;
	}

	public static List<Object[]> getResultList(PreparedStatement ps, Object[] params) throws SQLException {
		MySqlUtils.setQueryParams(ps, params);
		List<Object[]> resultList = new ArrayList<Object[]>();
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		Object[] objs = null;

		while (rs.next()) {
			objs = new Object[columnCount];
			for (int i = 0; i < columnCount; i++) {
				objs[i] = rs.getObject(i + 1);
			}
			resultList.add(objs);
		}

		MySqlUtils.close(rs);
		return resultList;
	}

	public static void setQueryParams(PreparedStatement ps, Object[] params) throws SQLException {
		if (null != params && params.length > 0) {
			for (int i = 0; i < params.length; i++) {
				String dataTypeFullName = params[i].getClass().getName();
				if ("java.lang.String".equals(dataTypeFullName)) {
					ps.setString(i + 1, (String) params[i]);
				} else if ("java.lang.Integer".equals(dataTypeFullName)) {
					ps.setInt(i + 1, (Integer) params[i]);
				} else if ("java.lang.Float".equals(dataTypeFullName)) {
					ps.setFloat(i + 1, (Float) params[i]);
				} else if ("java.lang.Double".equals(dataTypeFullName)) {
					ps.setDouble(i + 1, (Double) params[i]);
				} else if ("java.lang.Long".equals(dataTypeFullName)) {
					ps.setLong(i + 1, (Long) params[i]);
				} else if ("java.lang.Boolean".equals(dataTypeFullName)) {
					ps.setBoolean(i + 1, (Boolean) params[i]);
				} else if ("java.lang.Byte".equals(dataTypeFullName)) {
					ps.setByte(i + 1, (Byte) params[i]);
				} else if ("java.sql.Date".equals(dataTypeFullName)) {
					ps.setDate(i + 1, (java.sql.Date) params[i]);
				} else if ("java.lang.Object".equals(dataTypeFullName)) {
					ps.setObject(i + 1, params[i]);
				} else if ("javax.sql.rowset.serial.SerialClob".equals(dataTypeFullName)) {
					ps.setClob(i + 1, (Clob) params[i]);
				} else if ("java.sql.Timestamp".equals(dataTypeFullName)) {
					ps.setTimestamp(i + 1, (Timestamp) params[i]);
				}
			}
		}
	}

	public static void setQueryParams(CallableStatement cs, Object[] params) throws SQLException {
		if (null != params && params.length > 0) {
			for (int i = 0; i < params.length; i++) {
				String dataTypeFullName = params[i].getClass().getName();
				if ("java.lang.String".equals(dataTypeFullName)) {
					cs.setString(i + 1, (String) params[i]);
				} else if ("java.lang.Integer".equals(dataTypeFullName)) {
					cs.setInt(i + 1, (Integer) params[i]);
				} else if ("java.lang.Float".equals(dataTypeFullName)) {
					cs.setFloat(i + 1, (Float) params[i]);
				} else if ("java.lang.Double".equals(dataTypeFullName)) {
					cs.setDouble(i + 1, (Double) params[i]);
				} else if ("java.lang.Long".equals(dataTypeFullName)) {
					cs.setLong(i + 1, (Long) params[i]);
				} else if ("java.lang.Boolean".equals(dataTypeFullName)) {
					cs.setBoolean(i + 1, (Boolean) params[i]);
				} else if ("java.lang.Byte".equals(dataTypeFullName)) {
					cs.setByte(i + 1, (Byte) params[i]);
				} else if ("java.sql.Date".equals(dataTypeFullName)) {
					cs.setDate(i + 1, (java.sql.Date) params[i]);
				} else if ("java.lang.Object".equals(dataTypeFullName)) {
					cs.setObject(i + 1, params[i]);
				} else if ("javax.sql.rowset.serial.SerialClob".equals(dataTypeFullName)) {
					cs.setClob(i + 1, (Clob) params[i]);
				} else if ("java.sql.Timestamp".equals(dataTypeFullName)) {
					cs.setTimestamp(i + 1, (Timestamp) params[i]);
				}
			}
		}
	}

	public static void close(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public static void close(Statement stmt) {
		try {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public static void close(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public static void close(Statement stmt, Connection conn) {
		MySqlUtils.close(stmt);
		MySqlUtils.close(conn);
	}

	public static void close(ResultSet rs, Statement stmt, Connection conn) {
		MySqlUtils.close(rs);
		MySqlUtils.close(stmt);
		MySqlUtils.close(conn);
	}

	public static void main(String[] args) throws SQLException {
		Connection conn = MySqlUtils.getConn();
		CallableStatement cstmt = conn.prepareCall("{call ADD_DEPT(?, ?, ?, ?, ?, ?, ?)}");
		cstmt.setString(1, "4");
		cstmt.setString(2, "234");
		cstmt.setString(3, "456");
		cstmt.setString(4, "789");
		cstmt.setString(5, "");
		cstmt.setString(6, "");
		cstmt.setString(7, "china");
		ResultSet rs = cstmt.executeQuery();
		close(rs);
		close(cstmt);
		close(conn);
	}
}
